﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using XLETL.Model;
using XLETL.BLL;
using XLETL.Common;
using XLETL.ETLEngine;

namespace XLETL.TaskManager.Forms
{
    public partial class ProcessTaskDialog : Form
    {
        private TaskInfo taskToProcess = null;
        private DBInfo parentDb = null;
        private const string END_LN = "\r\n";

        #region Form Methods
        public ProcessTaskDialog(TaskInfo taskInfo, DBInfo dbInfo)
        {
            InitializeComponent();

            if (taskInfo == null)
            {
                throw new ArgumentNullException("taskInfo");
            }
            if (dbInfo == null)
            {
                throw new ArgumentNullException("dbInfo");
            }

            taskToProcess = taskInfo;
            parentDb = dbInfo;
            lblTaskName.Text = taskInfo.TaskDetails.Name;
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            openExcelFile.InitialDirectory = "c:\\";
            openExcelFile.Filter = "Excel 2007 files (*.xlsx)|*.xlsx|Macro-enabled Excel 2007 files (*.xlsm)|*.xlsm|Excel 2003 files (*.xls)|*.xls|All files (*.*)|*.*";
            openExcelFile.FilterIndex = 2;
            openExcelFile.RestoreDirectory = true;            

            if (openExcelFile.ShowDialog() == DialogResult.OK)
            {
                this.tbDataFile.Text = openExcelFile.FileName;
            }
        }

        private void btnProcess_Click(object sender, EventArgs e)
        {
            if (tbDataFile.Text != string.Empty)
            {
                Cursor.Current = Cursors.WaitCursor;
                this.progressBar1.Visible = true;

                this.rtbLog.Clear();

                if (chbTestRun.Checked)
                {
                  this.rtbLog.AppendText("------- Test Run has been started... ---------" + END_LN);
                }

                this.rtbLog.AppendText("Start ETL process..." + END_LN);

                // Begin processing asynchronously
                this.backgroundWorker1.RunWorkerAsync(new object[] { taskToProcess, parentDb, tbDataFile.Text, chbTestRun.Checked });
            }
            else
            {
                MessageBox.Show("Please, select spreadsheet file for ETL processing");
            }

        }

        private void btnRollback_Click(object sender, EventArgs e)
        {

        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        #endregion

        #region Background Worker

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            object[] arg = (object[])e.Argument;
            if (arg != null)
            {
                TaskInfo task = (TaskInfo)arg[0];
                DBInfo db = (DBInfo)arg[1];
                string fileName = (string)arg[2];
                bool testRun = (bool)arg[3];
                StartETLProcess(task, db, fileName, testRun);
            }
            else
            {
                MessageBox.Show("e.Argument is null");
            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            Cursor.Current = Cursors.Default;
            this.progressBar1.Visible = false;

            // Reset progress UI
            this.rtbLog.AppendText("ETL process done!");

            if (chbTestRun.Checked)
            {
              this.rtbLog.AppendText(END_LN + "------- Test Run has been completed. ---------");
            }
            
            // Was there an error?
            if (e.Error != null)
            {
                this.rtbLog.ForeColor = Color.Red;
                this.rtbLog.AppendText("Error:");                
                this.rtbLog.AppendText(e.Error.Message);
                this.rtbLog.ForeColor = Color.Black; // set the color back
                return;
            }           
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            string msg = (string)e.UserState;
            ShowProgress(msg);
        }

        void ShowProgress(string msg)
        {
            // Make sure we're on the UI thread
            System.Diagnostics.Debug.Assert(this.InvokeRequired == false);

            // Display progress in UI
            this.rtbLog.AppendText(msg);            
        }

        #endregion

        public void StartETLProcess(TaskInfo taskToProcess, DBInfo parentDb, string dataFile, bool testRun)
        {
            try
            {
                bool resultFlag = true;

                XLETL.ETLEngine.ETLEngine etlEng = new XLETL.ETLEngine.ETLEngine();
                // 1. Extract XL data
                ExcelHelper eh = new ExcelHelper();
                // Report initial progress
                this.backgroundWorker1.ReportProgress(0, "Extracting spreadsheet data..." + END_LN);
                
                DataTable loadedData = eh.LoadSpecifiedSheet(dataFile, taskToProcess.TaskDetails.SourceSheetName);

                // make sure there are some data 
                if (loadedData.Rows.Count > 0)
                {
                    this.backgroundWorker1.ReportProgress(20, "Extracting successful." + END_LN);
                    this.backgroundWorker1.ReportProgress(20, "Validating loaded spreadsheet structure..." + END_LN);
                    // 2. Validate loaded sheet
                    resultFlag = etlEng.ValidateLoadedSheetColumnConsistency(loadedData, taskToProcess);

                    if (resultFlag)
                    {
                        this.backgroundWorker1.ReportProgress(40, "Validation successful." + END_LN);
                        this.backgroundWorker1.ReportProgress(40, "Execute spreadsheet transformations..." + END_LN);
                        // 3. Execute Template Transformations
                        resultFlag = etlEng.ExecuteTransformation(loadedData, taskToProcess, parentDb);

                        if (resultFlag)
                        {
                            this.backgroundWorker1.ReportProgress(60, "Spreadsheet transformations completed." + END_LN);
                            this.backgroundWorker1.ReportProgress(60, "Execute target transformations..." + END_LN);
                            // 4. Executre Target Transformations
                            resultFlag = etlEng.ExecuteTargetColumnTransformation(loadedData, taskToProcess, parentDb);

                            // 5. Load Data
                            if (resultFlag)
                            {
                                this.backgroundWorker1.ReportProgress(80, "Target transformations completed." + END_LN);
                                this.backgroundWorker1.ReportProgress(80, "Building Sql scripts..." + END_LN);
                                string sqlQuery = etlEng.BuildSqlScripts(loadedData, taskToProcess, parentDb);                             

                                if (!string.IsNullOrEmpty(sqlQuery))
                                {
                                    this.backgroundWorker1.ReportProgress(90, "Sql scripts created." + END_LN);
                                    this.backgroundWorker1.ReportProgress(90, "Loading spreadsheet data into target database" + END_LN);
                                    resultFlag = etlEng.LoadDataIntoTargetTable(sqlQuery, parentDb, testRun);

                                    if (resultFlag)
                                    {
                                        this.backgroundWorker1.ReportProgress(100, "Loading spreadsheet data complete." + END_LN);
                                    }
                                    else this.backgroundWorker1.ReportProgress(100, "*Error loading spreadsheet data." + END_LN);
                                }
                                else this.backgroundWorker1.ReportProgress(100, "*Error building sql scripts." + END_LN);
                            }
                            else this.backgroundWorker1.ReportProgress(100, "*Error executing target transformations." + END_LN);
                        }
                        else this.backgroundWorker1.ReportProgress(100, "*Error executing spreadsheet transformations." + END_LN);
                    }
                    else this.backgroundWorker1.ReportProgress(100, "*Error validating spreadsheet structure." + END_LN);
                }
                else this.backgroundWorker1.ReportProgress(100, "*Error extracting spreadsheet data." + END_LN);

            }
            catch (Exception ex)
            {
                this.backgroundWorker1.ReportProgress(100, "*Error occurred during ETL process." + END_LN);
                throw ex;
            }
        }

        
    }
}
